/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package gw.reportbuilder;

import gw.reportbuilder.column.Column;
import gw.reportbuilder.filter.Filter;
import gw.reportbuilder.nucleo.DAO;
import gw.reportbuilder.nucleo.excecao.ExcecaoConexao;
import gw.reportbuilder.table.Table;
import java.io.IOException;
import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Sostenes
 */
public class ReportDAO extends DAO {

    public ReportDAO(Connection con) throws ExcecaoConexao, IOException {
        super(con);
    }

    public ReportDAO() throws ExcecaoConexao, IOException {
        super();
    }

    public void insertReport(Report report) throws SQLException {

        PreparedStatement prepSt = null;
        String sql = "INSERT INTO reportbuilder_report (title, table_name, table_label) values (?, ?, ?)";


        try {

            prepSt = getCon().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            prepSt.setString(1, report.getTitle());
            prepSt.setString(2, report.getTable().getName());
            prepSt.setString(3, report.getTable().getLabel());

            prepSt.executeUpdate();

            rs = prepSt.getGeneratedKeys();

            if (rs.next()) {

                report.setId(rs.getInt(1));
            }

            for (Column column : report.getColumns()) {

                insertColumn(column, report.getId(), Boolean.FALSE);
            }

            for (Filter filter : report.getFilters()) {

                insertColumn(filter.getColumn(), report.getId(), Boolean.TRUE);
            }

        } catch (SQLException ex) {

            rollBack(prepSt, ex);
            System.out.println(prepSt.toString());
            System.out.println(ex.getMessage());
            throw ex;

        } finally {

            fecharConexao();
        }
    }

    public void insertColumn(Column column, Integer reportId, Boolean filter) throws SQLException {

        PreparedStatement prepSt = null;
        String sql = "INSERT INTO reportbuilder_column (name, label, type, filter, report_id) values (?, ?, ?, ?, ?)";

        prepSt = prepareStatement(sql);

        prepSt.setString(1, column.getName());
        prepSt.setString(2, column.getLabel());
        prepSt.setString(3, column.getType());
        prepSt.setBoolean(4, filter);
        prepSt.setInt(5, reportId);
        prepSt.execute();
    }

    public Report getReport(int id) throws SQLException {

        PreparedStatement prepSt = null;
        Report report = null;

        try {

            String sql = "SELECT id, title, table_name, table_label FROM reportbuilder_report WHERE id = ?";

            prepSt = prepareStatement(sql);

            prepSt.setInt(1, id);

            rs = prepSt.executeQuery();

            if (rs.next()) {

                int reportId = rs.getInt("id");
                String title = rs.getString("title");
                String tableName = rs.getString("table_name");
                String tableLabel = rs.getString("table_label");

                Report.Builder builder = new Report.Builder(title, new Table(tableName, tableLabel));

                buildColumns(reportId, builder);

                report = builder.build();
            }

            return report;

        } catch (SQLException ex) {

            System.out.println(prepSt.toString());
            System.out.println(ex.getMessage());
            throw ex;

        } finally {

            fecharConexao();
        }
    }

    private void buildColumns(int reportId, Report.Builder builder) throws SQLException {

        PreparedStatement prepSt = null;

        String sql = "SELECT name, label, type, filter FROM reportbuilder_column WHERE report_id = ?";

        prepSt = prepareStatement(sql);

        prepSt.setInt(1, reportId);

        rs = prepSt.executeQuery();

        while (rs.next()) {

            String name = rs.getString("name");
            String label = rs.getString("label");
            String type = rs.getString("type");
            Boolean filter = rs.getBoolean("filter");

            Column column = new Column(name, label, type);

            if (filter) {

                builder.addFilter(new Filter(column));

            } else {

                builder.addColumn(column);
            }
        }
    }

    public void deleteReport(int id) throws SQLException {

        PreparedStatement prepSt = null;

        try {

            String sql = 
                    "DELETE FROM reportbuilder_column WHERE report_id = ?; "
                    + "DELETE FROM reportbuilder_report WHERE id = ?";

            prepSt = prepareStatement(sql);

            prepSt.setInt(1, id);
            prepSt.setInt(2, id);

            prepSt.execute();

        } catch (SQLException ex) {

            System.out.println(prepSt.toString());
            System.out.println(ex.getMessage());
            throw ex;

        } finally {

            fecharConexao();
        }

    }
    
    public boolean checkReportExists(String reportTitle) throws SQLException {
        
        PreparedStatement prepSt = null;

        try {

            String sql = "SELECT count(title) FROM reportbuilder_report WHERE title = ?";

            prepSt = prepareStatement(sql);

            prepSt.setString(1, reportTitle);

            rs = prepSt.executeQuery();
            
            if (rs.next()) {
                
                if (rs.getInt(1) > 0) {
                    return true;
                }
            }
            
            return false;

        } catch (SQLException ex) {

            System.out.println(prepSt.toString());
            System.out.println(ex.getMessage());
            throw ex;

        } finally {

            fecharConexao();
        }
        
    }

    public Map<Integer, String> listReports() throws SQLException {

        Map<Integer, String> result = new TreeMap<Integer, String>();
        PreparedStatement prepSt = null;

        try {

            String sql = "SELECT "
                    + "     rbr.id, "
                    + "     rbr.title "
                    + " FROM reportbuilder_report rbr";

            prepSt = prepareStatement(sql);

            rs = prepSt.executeQuery();

            while (rs.next()) {

                int reportId = rs.getInt("id");
                String title = rs.getString("title");

                result.put(reportId, title);
            }

            return result;

        } catch (SQLException ex) {

            System.out.println(prepSt.toString());
            System.out.println(ex.getMessage());
            throw ex;

        } finally {

            fecharConexao();
        }
    }

    public List<String> getColumnValues(String tableName, String columnName) throws SQLException {

        List<String> result = new ArrayList<String>();
        PreparedStatement prepSt = null;

        String sql = String.format("SELECT DISTINCT %s FROM %s WHERE %s IS NOT NULL ORDER BY 1 ASC", columnName, tableName, columnName);

        try {

            prepSt = prepareStatement(sql);

            rs = prepSt.executeQuery();

            while (rs.next()) {

                result.add(rs.getString(1));
            }

            return result;

        } catch (SQLException ex) {

            System.out.println(prepSt.toString());
            System.out.println(ex.getMessage());
            throw ex;

        } finally {

            fecharConexao();
        }
    }

    public Collection<Object[]> getReportResult(Report report) throws SQLException {

        List<Object[]> result = new ArrayList<Object[]>();
        PreparedStatement prepSt = null;
        Object[] row;


        try {

            prepSt = prepareStatement(report.toString());

            rs = prepSt.executeQuery();

            while (rs.next()) {

                ResultSetMetaData metaData = rs.getMetaData();

                int columnCount = metaData.getColumnCount();

                row = new Object[columnCount];

                int counter = 0;

                for (Column column : report.getColumns()) {

                    Object val = rs.getObject(column.getLabel());

                    row[counter++] = val;
                }

                result.add(row);
            }

            return result;

        } catch (SQLException ex) {

            Logger.getLogger(ReportBO.class.getName()).log(Level.SEVERE, null, ex);
            throw ex;

        } finally {

            fecharConexao();
        }
    }
}
